﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace DynamicToSql
{
    public static class SqlHelper
    {
        public static string GenerateSelect(ISession session, string name, PropertySelector selector)
        {
            var sql = new StringBuilder();

            if (selector == null)
            {
                sql.Append("SELECT * FROM ");
            }
            else if (selector.IncludeProperties.Count > 0)
            {
                sql.Append("SELECT ");
                bool first = true;
                foreach (var p in selector.IncludeProperties)
                {
                    if (!first)
                        sql.Append(", ");
                    sql.Append(session.NamingConverter.ToColumn(p));
                    first = false;
                }
                sql.Append(" FROM ");
            }
            else if (selector.ExcludeProperties.Count > 0)
            {
                throw new Exception("Exclude properties is not supported when generating select statements.");
            }

            sql.Append(session.NamingConverter.ToTableOrView(name));

            return sql.ToString();
        }

        public static string GenerateCount(ISession session, string name)
        {
            var sql = new StringBuilder();

            sql.Append("SELECT COUNT(*) FROM ");
            sql.Append(session.NamingConverter.ToTableOrView(name));

            return sql.ToString();
        }

        public static string GenerateInsert(ISession session, string name, Entity instance, PropertySelector selector)
        {
            var sql = new StringBuilder();

            sql.Append("INSERT INTO ");
            sql.Append(session.NamingConverter.ToTableOrView(name));
            sql.Append(" (");
            
            bool first = true;
            foreach (var p in instance.GetProperties())
            {
                if (selector == null)
                {
                    if (!first)
                        sql.Append(", ");

                    sql.Append(session.NamingConverter.ToColumn(p.Key));
                    first = false;
                }
                else if (selector.IncludeProperties.Count > 0)
                {
                    if (selector.IncludeProperties.Contains(p.Key))
                    {
                        if (!first)
                            sql.Append(", ");

                        sql.Append(session.NamingConverter.ToColumn(p.Key));
                        first = false;
                    }
                }
                else if (selector.ExcludeProperties.Count > 0)
                {
                    if (!selector.ExcludeProperties.Contains(p.Key))
                    {
                        if (!first)
                            sql.Append(", ");

                        sql.Append(session.NamingConverter.ToColumn(p.Key));
                        first = false;
                    }
                }
            }
            
            sql.Append(")");
            sql.Append(" VALUES (");
            
            first = true;
            foreach (var p in instance.GetProperties())
            {
                if (selector == null)
                {
                    if (!first)
                        sql.Append(", ");

                    sql.Append(session.ParameterPrefix);
                    sql.Append(p.Key);
                    first = false;
                }
                else if (selector.IncludeProperties.Count > 0)
                {
                    if (selector.IncludeProperties.Contains(p.Key))
                    {
                        if (!first)
                            sql.Append(", ");

                        sql.Append(session.ParameterPrefix);
                        sql.Append(p.Key);
                        first = false;
                    }
                }
                else if (selector.ExcludeProperties.Count > 0)
                {
                    if (!selector.ExcludeProperties.Contains(p.Key))
                    {
                        if (!first)
                            sql.Append(", ");

                        sql.Append(session.ParameterPrefix);
                        sql.Append(p.Key);
                        first = false;
                    }
                }
            }

            sql.Append(")");

            return sql.ToString();
        }

        public static string GenerateUpdate(ISession session, string name, Entity instance, PropertySelector selector)
        {
            var sql = new StringBuilder();

            sql.Append("UPDATE ");
            sql.Append(session.NamingConverter.ToTableOrView(name));
            sql.Append(" SET ");

            bool first = true;
            foreach (var p in instance.GetProperties())
            {
                if (selector == null)
                {
                    if (!first)
                        sql.Append(", ");

                    sql.Append(session.NamingConverter.ToColumn(p.Key));
                    sql.Append("=");
                    sql.Append(session.ParameterPrefix);
                    sql.Append(p.Key);
                    first = false;
                }
                else if (selector.IncludeProperties.Count > 0)
                {
                    if (selector.IncludeProperties.Contains(p.Key))
                    {
                        if (!first)
                            sql.Append(", ");

                        sql.Append(session.NamingConverter.ToColumn(p.Key));
                        sql.Append("=");
                        sql.Append(session.ParameterPrefix);
                        sql.Append(p.Key);
                        first = false;
                    }
                }
                else if (selector.ExcludeProperties.Count > 0)
                {
                    if (!selector.ExcludeProperties.Contains(p.Key))
                    {
                        if (!first)
                            sql.Append(", ");

                        sql.Append(session.NamingConverter.ToColumn(p.Key));
                        sql.Append("=");
                        sql.Append(session.ParameterPrefix);
                        sql.Append(p.Key);
                        first = false;
                    }
                }
            }

            return sql.ToString();
        }

        public static string GenerateDelete(ISession session, string name)
        {
            var sql = new StringBuilder();
            
            sql.Append("DELETE FROM ");
            sql.Append(session.NamingConverter.ToTableOrView(name));

            return sql.ToString();
        }

        public static string GenerateCriteria(ISession session, Criteria criteria)
        {
            int paramIndex = 0;
            return GenerateCriteria(session, criteria, ref paramIndex);
        }

        private static string GenerateCriteria(ISession session, Criteria criteria, ref int paramIndex)
        {
            int i = 0;
            var sql = new StringBuilder();

            string operatorStr = null;
            if (criteria.BooleanOperator == BooleanOperator.And)
                operatorStr = " AND ";
            else if (criteria.BooleanOperator == BooleanOperator.Or)
                operatorStr = " OR ";

            foreach (object criteron in criteria.Criterions)
            {
                if (criteron is Expression)
                {
                    var exp = (Expression)criteron;

                    var property = exp.Property;

                    if (!string.IsNullOrEmpty(property.SqlFunction))
                        sql.Append(string.Format(property.SqlFunction, session.NamingConverter.ToColumn(property.Name)));
                    else
                        sql.Append(session.NamingConverter.ToColumn(property.Name));

                    if (exp.ComparisonOperator == ComparisonOperator.Equal)
                    {
                        if (exp.Values[0] == null)
                            sql.Append(" IS NULL");
                        else
                        {
                            sql.Append(" = ");
                            sql.Append(session.ParameterPrefix);
                            sql.Append("p");
                            sql.Append(paramIndex++);
                        }
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.NotEqual)
                    {
                        if (exp.Values[0] == null)
                            sql.Append(" IS NOT NULL");
                        else
                        {
                            sql.Append(" <> ");
                            sql.Append(session.ParameterPrefix);
                            sql.Append("p");
                            sql.Append(paramIndex++);
                        }
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.GreaterThan)
                    {
                        sql.Append(" > ");
                        sql.Append(session.ParameterPrefix);
                        sql.Append("p");
                        sql.Append(paramIndex++);
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.GreaterThanOrEqual)
                    {
                        sql.Append(" >= ");
                        sql.Append(session.ParameterPrefix);
                        sql.Append("p");
                        sql.Append(paramIndex++);
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.LessThan)
                    {
                        sql.Append(" < ");
                        sql.Append(session.ParameterPrefix);
                        sql.Append("p");
                        sql.Append(paramIndex++);
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.LessThanOrEqual)
                    {
                        sql.Append(" <= ");
                        sql.Append(session.ParameterPrefix);
                        sql.Append("p");
                        sql.Append(paramIndex++);
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.Like)
                    {
                        sql.Append(" LIKE ");
                        sql.Append(session.ParameterPrefix);
                        sql.Append("p");
                        sql.Append(paramIndex++);
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.In)
                    {
                        sql.Append(" IN (");
                        bool first = true;
                        for (int n = 0; n < exp.Values.Count; n++)
                        {
                            if (!first)
                                sql.Append(", ");
                            sql.Append(session.ParameterPrefix);
                            sql.Append("p");
                            sql.Append(paramIndex++);
                            first = false;
                        }
                        sql.Append(")");
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.Between)
                    {
                        sql.Append(" BETWEEN ");
                        sql.Append(session.ParameterPrefix);
                        sql.Append("p");
                        sql.Append(paramIndex++);
                        sql.Append(" AND ");
                        sql.Append(session.ParameterPrefix);
                        sql.Append("p");
                        sql.Append(paramIndex++);
                    }

                }
                else if (criteron is Criteria)
                {
                    var c = (Criteria)criteron;
                    sql.Append("(");
                    sql.Append(SqlHelper.GenerateCriteria(session, c, ref paramIndex));
                    sql.Append(")");
                }

                if (i != criteria.Criterions.Count - 1)
                {
                    sql.Append(operatorStr);
                }

                i++;
            }

            return sql.ToString();
        }

    }
}
